***********************************************************************************
********"Macroprudential Regulation, Quantitative Easing, and Bank Lending"********
***********************************************************************************
* This file produces some datasets that will be 
* used for the 2019 analysis. In particular:
* - a dataset with banks' securities
* - a dataset with pre-QE announcement variables
* - a dataset with borrowing from the ECB
***********************************************************************************
***********************************************************************************


*==================================================================================
**# Bookmark (input)
clear *
import sas ENTE_SEGN CENSITO_CRITTOG TIME DISBUR COM COMUNE_DIP using $inputdirectory/bloans2019, case(lower)
run RO_creditregister tm(2019m8) tm(2019m8) 0

collapse (sum) disbur, by(bankid time)
rename disbur exbdisbur
recode exbdisbur (.=0)
xtset bankid time

* No assets, not in the sample
merge 1:1 bankid time using $inputdirectory/INB_assetsi, keepus(assets)
keep if _merge==3
drop _merge
rename assets exassets

* No bank type, not in the sample
merge 1:1 bankid using $inputdirectory/INB_type2019, keepus(type)
keep if _merge==3
drop _merge

merge 1:1 bankid using $inputdirectory/INB_gru2019, keepus(gru)
keep if _merge!=2
drop _merge
replace gru=bankid if gru==.
gen xxx=type if bankid==gru
egen grutype=max(xxx), by(gru)
replace grutype=10 if grutype==.
drop xxx

* No postal service and keep only banks with a joint-stock holding company
* LINE NOT SHOWN
keep if grutype==10

* Add a long list of variables
merge 1:1 bankid using $inputdirectory/INB_fs2019, keepus(cash)
keep if _merge!=2
drop _merge
rename cash excash
recode excash (.=0)

merge 1:1 bankid using $inputdirectory/INB_netintbank2019, keepus(netinterbank)
keep if _merge!=2
drop _merge
rename netinterbank exnetinterbank
recode exnetinterbank (.=0)

merge 1:1 bankid time using $inputdirectory/INB_depo, keepus(depo)
keep if _merge!=2
rename depo exdepo
recode exdepo (.=0)
drop _merge

merge 1:1 bankid using $inputdirectory/INB_tltro2019, keepus(extltro)
keep if _merge!=2
recode extltro (.=0)
drop _merge

merge m:1 gru using $inputdirectory/INB_capital2019, keepus(t1ratio)
keep if _merge!=2
drop _merge
rename t1ratio ext1r

**# Bookmark (temporary dataset and lauch of satellite code)
compress
save temp, replace
do SA_satellite2019

**# Bookmark (input)
clear all
import sas using $inputdirectory/isins2019

rename *, lower
rename dat time
rename date_mat time_mat
rename ente_segn bankid
rename fonte source
egen isin=group(cod_isin)
tostring(time), replace
gen xxx=date(time, "YMD")
rename time oldtime
gen time=mofd(xxx)
drop xxx
format time %tm
tostring(time_mat), replace
gen xxx=date(time_mat, "YMD")
rename time_mat oldtime_mat
gen time_mat=mofd(xxx)
drop xxx
format time_mat %tm
replace time_mat=tm(2200m12) if time_mat==tm(9999m12)
gen monthtom=time_mat-time
drop if monthtom<0

* Group securities according to their accounting treatment
replace book=-book if portaf_contab==508|portaf_contab==409
gen portfolio=.
replace portfolio=1 if inlist(portaf_contab,403,405,406)
replace portfolio=2 if inlist(portaf_contab,407)
replace portfolio=3 if inlist(portaf_contab,401,402,0,408)
drop if portfolio==.

fillin bankid time portfolio
replace isin=0 if isin==.
replace book=0 if book==.

egen id=group(isin bankid portfolio)
collapse (sum) book (first) cod_isin isin bankid portfolio (max) staemitit setemi stanziabilita time_mat monthtom _fillin, by(id time)
xtset id time

* Banks in the sample
merge m:1 bankid using temp
keep if _merge==3
drop _merge

merge m:1 bankid using $inputdirectory/INB_gru2019, keepus(gru)
keep if _merge!=2
drop _merge
replace gru=bankid if gru==.
drop id
drop bankid
rename gru bankid

egen id=group(isin bankid portfolio)

collapse (sum) book exassets excash exdepo exbdisbur exnetinterbank extltro (first) cod_isin isin bankid portfolio ext1r (max) staemitit setemi stanziabilita time_mat monthtom (min) _fillin , by(id time)

**# Bookmark 4 (saving dataset - permanent intermediate dataset to be used later)
compress
save $inputdirectory/INB_portfolio2019, replace


*===============================================================================
**# Bookmark (input)
foreach loop1 in tm(2019m8) tm(2019m2) {
global datepre "`loop1'"

use $inputdirectory/INB_portfolio2019, clear
keep if time==${datepre}

* QE-eligible securities
gen die=0
if $datepre==tm(2019m8) {
replace die=1 if inlist(staemitit,8,9,101,94,257,67,28,29,40,86,259,92,258,105,50,55,260,276,477,479,480,574,576,577,580,587,588,591,595,799,807,812,814,816,853,910,911,913,916,921,929,940,945,950,959,974,32)&inlist(setemi,100,704,770,173,706,708,121,120)&inlist(stanziabilita,23,24)&(monthtom>=15)&(monthtom<=375)&(monthtom!=.)
}
if $datepre==tm(2019m2) {
replace die=1 if inlist(staemitit,8,9,101,94,257,67,28,29,40,86,259,92,258,105,50,55,260,276,477,479,480,574,576,577,580,587,588,591,595,799,807,812,814,816,853,910,911,913,916,921,929,940,945,950,959,974,32)&inlist(setemi,100,704,770,173,706,708,121,120)&inlist(stanziabilita,23,24)&(monthtom>=21)&(monthtom<=381)&(monthtom!=.)
}

gen book2=book
replace book=0 if die!=1

foreach loop2 in 0 1 2 3 4 5 {
global portfolio "`loop2'"

preserve
if ("${portfolio}"=="0") { 
}

if  ("${portfolio}"=="1"|"${portfolio}"=="2"|"${portfolio}"=="3") {
keep if portfolio==${portfolio}
}

if ("${portfolio}"=="4") { 
keep if portfolio==2|portfolio==3
recode portfolio (2=4) (3=4)
}

if ("${portfolio}"=="5") { 
keep if portfolio==1|portfolio==2
recode portfolio (1=5) (2=5)
}

merge m:1 bankid time using $inputdirectory/INB_ecbdepo2019, keepus(ecbdepo)
keep if _merge!=2
drop _merge
rename ecbdepo execbdepo
recode execbdepo  (.=0)

merge m:1 bankid using $inputdirectory/INB_cbabser2019, keepus(excbabserd0)
keep if _merge!=2
drop _merge
recode excbabserd0  (.=0)

merge m:1 bankid using $inputdirectory/INB_cbabs42019, keepus(excbabs4)
keep if _merge!=2
drop _merge
recode excbabs4 (.=0)

merge m:1 bankid using $inputdirectory/INB_cspp42019, keepus(excspp4)
keep if _merge!=2
drop _merge
recode excspp4 (.=0)

merge m:1 bankid using $inputdirectory/INB_unsuallow2019, keepus(exunusallow)
keep if _merge!=2
drop _merge
recode exunusallow (.=0)

collapse (sum) book book2 (max) exassets excash exdepo execbdepo exbdisbur exnetinterbank ext1r excbabserd0 excbabs4 extltro excspp4 exunusallow, by(bankid)

gen e1${portfolio}=100*(book/exassets)
egen xxx = pctile(e1${portfolio}) if e1${portfolio}!=., p(66)
gen dtop66e1${portfolio}=(e1${portfolio}>xxx) if e1${portfolio}!=.
drop xxx
egen xxx = pctile(e1${portfolio}) if e1${portfolio}!=., p(33)
gen dtop33e1${portfolio}=(e1${portfolio}>xxx) if e1${portfolio}!=.
drop xxx
gen dmid6633e1${portfolio}=0
replace dmid6633e1${portfolio}=1 if dtop33e1${portfolio}==1&dtop66e1${portfolio}==0

gen ema${portfolio}=100*(book2-book)/(exassets)
gen exlnassets=ln(exassets)
gen excashas=100*(excash+execbdepo)/(exassets)
gen exdepoas=100*(exdepo)/(exassets)
gen exbdisburas=100*(exbdisbur)/(exassets)
gen exnetinterbankas=100*(exnetinterbank)/(exassets)
gen extltroas=100*(0.3*extltro)/(exassets)

compress
save e1${portfolio}, replace
restore
}

use e13, clear

merge m:1 bankid using e10, keepus(e10)
keep if _merge!=2
drop _merge

merge m:1 bankid using e11, keepus(e11)
keep if _merge!=2
drop _merge

merge m:1 bankid using e12, keepus(e12 ema2)
keep if _merge!=2
drop _merge

merge m:1 bankid using e14, keepus(e14 dtop* dmid* ema4)
keep if _merge!=2
drop _merge

merge m:1 bankid using e15, keepus(e15)
keep if _merge!=2
drop _merge

erase e10.dta
erase e11.dta
erase e12.dta
erase e13.dta
erase e14.dta
erase e15.dta

global nfile 2019m8

if $datepre==tm(2019m2) {
foreach var of varlist _all {
rename `var' `var'f
}
rename bankidf bankid
global nfile 2019m2
}


**# Bookmark (saving dataset - file with different pre-announcement variables)
compress
save $inputdirectory/INB_preann$nfile, replace
}

*===============================================================================
**# Bookmark (input)
clear *
clear all
import sas using $inputdirectory/refin, case(lower)

rename dat date
tostring(date), replace
gen xxx=date(date, "YMD")
drop date
gen time=mofd(xxx)
drop xxx
format time %tm
rename rifinbil refin
rename ente_segn bankid
drop abi

merge m:1 bankid using $inputdirectory/INB_gru2019, keepus(gru)
keep if _merge!=2
drop _merge
replace gru=bankid if gru==.
drop bankid
rename gru bankid

collapse (sum) refin, by(bankid time)

merge m:1 bankid using $inputdirectory/INB_preann2019m8, keepus(exassets)
drop exassets
keep if _merge==3|_merge==2
drop _merge
xtset bankid time
replace time=tm(2019m8) if time==.
tsfill, full
recode refin (.=0)
gen lncrefin=100*(ln(refin+1)-ln(L.refin+1))

**# Bookmark (saving dataset - dynamic of borrowing from the ECB)
compress
save $inputdirectory/INB_refinm2019, replace
